--    Program: 		CAPPOIMP_ORGANIZATION_ACCESS.sql
--    Description:  Import PO ORGANIZATION ACCESS information 
--    Revision:     1st     
--    Parameter: 	
--    Created By:   Kenny Leung
--    Date:         27-Jan-2006

set doc off;      
set verify off;
set serveroutput on size 1000000;
whenever sqlerror exit failure rollback;

declare   
   l_processed_count		number;
   l_rejected_count 		number;
   l_err_msg				varchar2(2000);
   l_err_msg_tmp			varchar2(2000);
                    		                 		
   cursor cur_mtl_parameter is
      select * from CRST_MTL_PARAMETERS;

begin
   l_processed_count := 0;
   l_rejected_count  := 0;
   l_err_msg := '';
   
   for cur in cur_mtl_parameter loop   	
     l_err_msg_tmp := '';            
     
     begin
       -- import data
       insert into MTL_PARAMETERS
       (
            ORGANIZATION_ID                 ,
            LAST_UPDATE_DATE                ,
            LAST_UPDATED_BY                 ,
            CREATION_DATE                   ,
            CREATED_BY                      ,
            LAST_UPDATE_LOGIN               ,
            ORGANIZATION_CODE               ,
            MASTER_ORGANIZATION_ID          ,
            PRIMARY_COST_METHOD             ,
            COST_ORGANIZATION_ID            ,
            DEFAULT_MATERIAL_COST_ID        ,
            CALENDAR_EXCEPTION_SET_ID       ,
            CALENDAR_CODE                   ,
            GENERAL_LEDGER_UPDATE_CODE      ,
            DEFAULT_ATP_RULE_ID             ,
            DEFAULT_PICKING_RULE_ID         ,
            DEFAULT_LOCATOR_ORDER_VALUE     ,
            DEFAULT_SUBINV_ORDER_VALUE      ,
            NEGATIVE_INV_RECEIPT_CODE       ,
            STOCK_LOCATOR_CONTROL_CODE      ,
            MATERIAL_ACCOUNT                ,
            MATERIAL_OVERHEAD_ACCOUNT       ,
            MATL_OVHD_ABSORPTION_ACCT       ,
            RESOURCE_ACCOUNT                ,
            PURCHASE_PRICE_VAR_ACCOUNT      ,
            AP_ACCRUAL_ACCOUNT              ,
            OVERHEAD_ACCOUNT                ,
            OUTSIDE_PROCESSING_ACCOUNT      ,
            INTRANSIT_INV_ACCOUNT           ,
            INTERORG_RECEIVABLES_ACCOUNT    ,
            INTERORG_PRICE_VAR_ACCOUNT      ,
            INTERORG_PAYABLES_ACCOUNT       ,
            COST_OF_SALES_ACCOUNT           ,
            ENCUMBRANCE_ACCOUNT             ,
            PROJECT_COST_ACCOUNT            ,
            INTERORG_TRANSFER_CR_ACCOUNT    ,
            MATL_INTERORG_TRANSFER_CODE     ,
            INTERORG_TRNSFR_CHARGE_PERCENT  ,
            SOURCE_ORGANIZATION_ID          ,
            SOURCE_SUBINVENTORY             ,
            SOURCE_TYPE                     ,
            ORG_MAX_WEIGHT                  ,
            ORG_MAX_WEIGHT_UOM_CODE         ,
            ORG_MAX_VOLUME                  ,
            ORG_MAX_VOLUME_UOM_CODE         ,
            SERIAL_NUMBER_TYPE              ,
            AUTO_SERIAL_ALPHA_PREFIX        ,
            START_AUTO_SERIAL_NUMBER        ,
            AUTO_LOT_ALPHA_PREFIX           ,
            LOT_NUMBER_UNIQUENESS           ,
            LOT_NUMBER_GENERATION           ,
            LOT_NUMBER_ZERO_PADDING         ,
            LOT_NUMBER_LENGTH               ,
            STARTING_REVISION               ,
            ATTRIBUTE_CATEGORY              ,
            ATTRIBUTE1                      ,
            ATTRIBUTE2                      ,
            ATTRIBUTE3                      ,
            ATTRIBUTE4                      ,
            ATTRIBUTE5                      ,
            ATTRIBUTE6                      ,
            ATTRIBUTE7                      ,
            ATTRIBUTE8                      ,
            ATTRIBUTE9                      ,
            ATTRIBUTE10                     ,
            ATTRIBUTE11                     ,
            ATTRIBUTE12                     ,
            ATTRIBUTE13                     ,
            ATTRIBUTE14                     ,
            ATTRIBUTE15                     ,
            DEFAULT_DEMAND_CLASS            ,
            ENCUMBRANCE_REVERSAL_FLAG       ,
            MAINTAIN_FIFO_QTY_STACK_TYPE    ,
            INVOICE_PRICE_VAR_ACCOUNT       ,
            AVERAGE_COST_VAR_ACCOUNT        ,
            SALES_ACCOUNT                   ,
            EXPENSE_ACCOUNT                 ,
            SERIAL_NUMBER_GENERATION        ,
            REQUEST_ID                      ,
            PROGRAM_APPLICATION_ID          ,
            PROGRAM_ID                      ,
            PROGRAM_UPDATE_DATE             ,
            GLOBAL_ATTRIBUTE_CATEGORY       ,
            GLOBAL_ATTRIBUTE1               ,
            GLOBAL_ATTRIBUTE2               ,
            GLOBAL_ATTRIBUTE3               ,
            GLOBAL_ATTRIBUTE4               ,
            GLOBAL_ATTRIBUTE5               ,
            GLOBAL_ATTRIBUTE6               ,
            GLOBAL_ATTRIBUTE7               ,
            GLOBAL_ATTRIBUTE8               ,
            GLOBAL_ATTRIBUTE9               ,
            GLOBAL_ATTRIBUTE10              ,
            GLOBAL_ATTRIBUTE11              ,
            GLOBAL_ATTRIBUTE12              ,
            GLOBAL_ATTRIBUTE13              ,
            GLOBAL_ATTRIBUTE14              ,
            GLOBAL_ATTRIBUTE15              ,
            GLOBAL_ATTRIBUTE16              ,
            GLOBAL_ATTRIBUTE17              ,
            GLOBAL_ATTRIBUTE18              ,
            GLOBAL_ATTRIBUTE19              ,
            GLOBAL_ATTRIBUTE20              ,
            MAT_OVHD_COST_TYPE_ID           ,
            PROJECT_REFERENCE_ENABLED       ,
            PM_COST_COLLECTION_ENABLED      ,
            PROJECT_CONTROL_LEVEL           ,
            AVG_RATES_COST_TYPE_ID          ,
            TXN_APPROVAL_TIMEOUT_PERIOD     ,
            MO_SOURCE_REQUIRED              ,
            MO_PICK_CONFIRM_REQUIRED        ,
            MO_APPROVAL_TIMEOUT_ACTION      ,
            BORRPAY_MATL_VAR_ACCOUNT        ,
            BORRPAY_MOH_VAR_ACCOUNT         ,
            BORRPAY_RES_VAR_ACCOUNT         ,
            BORRPAY_OSP_VAR_ACCOUNT         ,
            BORRPAY_OVH_VAR_ACCOUNT         ,
            PROCESS_ENABLED_FLAG            ,
            PROCESS_ORGN_CODE               ,
            WSM_ENABLED_FLAG                ,
            DEFAULT_COST_GROUP_ID           ,
            LPN_PREFIX                      ,
            LPN_SUFFIX                      ,
            LPN_STARTING_NUMBER             ,
            WMS_ENABLED_FLAG                ,
            PREGEN_PUTAWAY_TASKS_FLAG       ,
            REGENERATION_INTERVAL           ,
            TIMEZONE_ID                     ,
            MAX_PICKS_BATCH                 ,
            DEFAULT_WMS_PICKING_RULE_ID     ,
            DEFAULT_PUT_AWAY_RULE_ID        ,
            DEFAULT_TASK_ASSIGN_RULE_ID     ,
            DEFAULT_LABEL_COMP_RULE_ID      ,
            DEFAULT_CARTON_RULE_ID          ,
            DEFAULT_CYC_COUNT_HEADER_ID     ,
            CROSSDOCK_FLAG                  ,
            CARTONIZATION_FLAG              ,
            COST_CUTOFF_DATE                ,
            ENABLE_COSTING_BY_CATEGORY      ,
            COST_GROUP_ACCOUNTING           ,
            ALLOCATE_SERIAL_FLAG            ,
            DEFAULT_PICK_TASK_TYPE_ID       ,
            DEFAULT_CC_TASK_TYPE_ID         ,
            DEFAULT_PUTAWAY_TASK_TYPE_ID    ,
            DEFAULT_REPL_TASK_TYPE_ID       ,
            EAM_ENABLED_FLAG                ,
            MAINT_ORGANIZATION_ID           ,
            PRIORITIZE_WIP_JOBS             ,
            DEFAULT_CROSSDOCK_SUBINVENTORY  ,
            SKIP_TASK_WAITING_MINUTES       ,
            QA_SKIPPING_INSP_FLAG           ,
            DEFAULT_CROSSDOCK_LOCATOR_ID    ,
            DEFAULT_MOXFER_TASK_TYPE_ID     ,
            DEFAULT_MOISSUE_TASK_TYPE_ID    ,
            DEFAULT_MATL_OVHD_COST_ID       ,
            DISTRIBUTED_ORGANIZATION_FLAG   ,
            CARRIER_MANIFESTING_FLAG        ,
            DISTRIBUTION_ACCOUNT_ID         ,
            DIRECT_SHIPPING_ALLOWED         ,
            DEFAULT_PICK_OP_PLAN_ID         ,
            MAX_CLUSTERS_ALLOWED            ,
            CONSIGNED_FLAG                  ,
            CARTONIZE_SALES_ORDERS          ,
            CARTONIZE_MANUFACTURING         
       )
       values
       (
            CAPBRIMP_MAPPING.GetOrganizationMapping(cur.ORGANIZATION_ID),
            cur.LAST_UPDATE_DATE                ,
            CAPBRIMP_MAPPING.GetUserMapping(cur.LAST_UPDATED_BY),
            cur.CREATION_DATE                   ,
            CAPBRIMP_MAPPING.GetUserMapping(cur.CREATED_BY),
            cur.LAST_UPDATE_LOGIN               ,
            cur.ORGANIZATION_CODE               ,
            CAPBRIMP_MAPPING.GetOrganizationMapping(cur.MASTER_ORGANIZATION_ID),
            cur.PRIMARY_COST_METHOD             ,
            CAPBRIMP_MAPPING.GetOrganizationMapping(cur.COST_ORGANIZATION_ID),
            cur.DEFAULT_MATERIAL_COST_ID        ,
            cur.CALENDAR_EXCEPTION_SET_ID       ,
            cur.CALENDAR_CODE                   ,
            cur.GENERAL_LEDGER_UPDATE_CODE      ,
            cur.DEFAULT_ATP_RULE_ID             ,
            cur.DEFAULT_PICKING_RULE_ID         ,
            cur.DEFAULT_LOCATOR_ORDER_VALUE     ,
            cur.DEFAULT_SUBINV_ORDER_VALUE      ,
            cur.NEGATIVE_INV_RECEIPT_CODE       ,
            cur.STOCK_LOCATOR_CONTROL_CODE      ,
            CAPBRIMP_MAPPING.GetCCIDMapping(cur.MATERIAL_ACCOUNT),
            CAPBRIMP_MAPPING.GetCCIDMapping(cur.MATERIAL_OVERHEAD_ACCOUNT),
            CAPBRIMP_MAPPING.GetCCIDMapping(cur.MATL_OVHD_ABSORPTION_ACCT),
            CAPBRIMP_MAPPING.GetCCIDMapping(cur.RESOURCE_ACCOUNT),
            CAPBRIMP_MAPPING.GetCCIDMapping(cur.PURCHASE_PRICE_VAR_ACCOUNT),
            CAPBRIMP_MAPPING.GetCCIDMapping(cur.AP_ACCRUAL_ACCOUNT),
            CAPBRIMP_MAPPING.GetCCIDMapping(cur.OVERHEAD_ACCOUNT),
            CAPBRIMP_MAPPING.GetCCIDMapping(cur.OUTSIDE_PROCESSING_ACCOUNT),
            CAPBRIMP_MAPPING.GetCCIDMapping(cur.INTRANSIT_INV_ACCOUNT),
            CAPBRIMP_MAPPING.GetCCIDMapping(cur.INTERORG_RECEIVABLES_ACCOUNT),
            CAPBRIMP_MAPPING.GetCCIDMapping(cur.INTERORG_PRICE_VAR_ACCOUNT),
            CAPBRIMP_MAPPING.GetCCIDMapping(cur.INTERORG_PAYABLES_ACCOUNT),
            CAPBRIMP_MAPPING.GetCCIDMapping(cur.COST_OF_SALES_ACCOUNT),
            CAPBRIMP_MAPPING.GetCCIDMapping(cur.ENCUMBRANCE_ACCOUNT),
            CAPBRIMP_MAPPING.GetCCIDMapping(cur.PROJECT_COST_ACCOUNT),
            CAPBRIMP_MAPPING.GetCCIDMapping(cur.INTERORG_TRANSFER_CR_ACCOUNT),
            cur.MATL_INTERORG_TRANSFER_CODE     ,
            cur.INTERORG_TRNSFR_CHARGE_PERCENT  ,
            CAPBRIMP_MAPPING.GetCCIDMapping(cur.SOURCE_ORGANIZATION_ID),
            cur.SOURCE_SUBINVENTORY             ,
            cur.SOURCE_TYPE                     ,
            cur.ORG_MAX_WEIGHT                  ,
            cur.ORG_MAX_WEIGHT_UOM_CODE         ,
            cur.ORG_MAX_VOLUME                  ,
            cur.ORG_MAX_VOLUME_UOM_CODE         ,
            cur.SERIAL_NUMBER_TYPE              ,
            cur.AUTO_SERIAL_ALPHA_PREFIX        ,
            cur.START_AUTO_SERIAL_NUMBER        ,
            cur.AUTO_LOT_ALPHA_PREFIX           ,
            cur.LOT_NUMBER_UNIQUENESS           ,
            cur.LOT_NUMBER_GENERATION           ,
            cur.LOT_NUMBER_ZERO_PADDING         ,
            cur.LOT_NUMBER_LENGTH               ,
            cur.STARTING_REVISION               ,
            cur.ATTRIBUTE_CATEGORY              ,
            cur.ATTRIBUTE1                      ,
            cur.ATTRIBUTE2                      ,
            cur.ATTRIBUTE3                      ,
            cur.ATTRIBUTE4                      ,
            cur.ATTRIBUTE5                      ,
            cur.ATTRIBUTE6                      ,
            cur.ATTRIBUTE7                      ,
            cur.ATTRIBUTE8                      ,
            cur.ATTRIBUTE9                      ,
            cur.ATTRIBUTE10                     ,
            cur.ATTRIBUTE11                     ,
            cur.ATTRIBUTE12                     ,
            cur.ATTRIBUTE13                     ,
            cur.ATTRIBUTE14                     ,
            cur.ATTRIBUTE15                     ,
            cur.DEFAULT_DEMAND_CLASS            ,
            cur.ENCUMBRANCE_REVERSAL_FLAG       ,
            cur.MAINTAIN_FIFO_QTY_STACK_TYPE    ,
            cur.INVOICE_PRICE_VAR_ACCOUNT       ,
            cur.AVERAGE_COST_VAR_ACCOUNT        ,
            CAPBRIMP_MAPPING.GetCCIDMapping(cur.SALES_ACCOUNT),
            CAPBRIMP_MAPPING.GetCCIDMapping(cur.EXPENSE_ACCOUNT),
            cur.SERIAL_NUMBER_GENERATION        ,
            cur.REQUEST_ID                      ,
            cur.PROGRAM_APPLICATION_ID          ,
            cur.PROGRAM_ID                      ,
            cur.PROGRAM_UPDATE_DATE             ,
            cur.GLOBAL_ATTRIBUTE_CATEGORY       ,
            cur.GLOBAL_ATTRIBUTE1               ,
            cur.GLOBAL_ATTRIBUTE2               ,
            cur.GLOBAL_ATTRIBUTE3               ,
            cur.GLOBAL_ATTRIBUTE4               ,
            cur.GLOBAL_ATTRIBUTE5               ,
            cur.GLOBAL_ATTRIBUTE6               ,
            cur.GLOBAL_ATTRIBUTE7               ,
            cur.GLOBAL_ATTRIBUTE8               ,
            cur.GLOBAL_ATTRIBUTE9               ,
            cur.GLOBAL_ATTRIBUTE10              ,
            cur.GLOBAL_ATTRIBUTE11              ,
            cur.GLOBAL_ATTRIBUTE12              ,
            cur.GLOBAL_ATTRIBUTE13              ,
            cur.GLOBAL_ATTRIBUTE14              ,
            cur.GLOBAL_ATTRIBUTE15              ,
            cur.GLOBAL_ATTRIBUTE16              ,
            cur.GLOBAL_ATTRIBUTE17              ,
            cur.GLOBAL_ATTRIBUTE18              ,
            cur.GLOBAL_ATTRIBUTE19              ,
            cur.GLOBAL_ATTRIBUTE20              ,
            cur.MAT_OVHD_COST_TYPE_ID           ,
            cur.PROJECT_REFERENCE_ENABLED       ,
            cur.PM_COST_COLLECTION_ENABLED      ,
            cur.PROJECT_CONTROL_LEVEL           ,
            cur.AVG_RATES_COST_TYPE_ID          ,
            cur.TXN_APPROVAL_TIMEOUT_PERIOD     ,
            cur.MO_SOURCE_REQUIRED              ,
            cur.MO_PICK_CONFIRM_REQUIRED        ,
            cur.MO_APPROVAL_TIMEOUT_ACTION      ,
            CAPBRIMP_MAPPING.GetCCIDMapping(cur.BORRPAY_MATL_VAR_ACCOUNT),
            CAPBRIMP_MAPPING.GetCCIDMapping(cur.BORRPAY_MOH_VAR_ACCOUNT),
            CAPBRIMP_MAPPING.GetCCIDMapping(cur.BORRPAY_RES_VAR_ACCOUNT),
            CAPBRIMP_MAPPING.GetCCIDMapping(cur.BORRPAY_OSP_VAR_ACCOUNT),
            CAPBRIMP_MAPPING.GetCCIDMapping(cur.BORRPAY_OVH_VAR_ACCOUNT),
            cur.PROCESS_ENABLED_FLAG            ,
            cur.PROCESS_ORGN_CODE               ,
            cur.WSM_ENABLED_FLAG                ,
            cur.DEFAULT_COST_GROUP_ID           ,
            cur.LPN_PREFIX                      ,
            cur.LPN_SUFFIX                      ,
            cur.LPN_STARTING_NUMBER             ,
            cur.WMS_ENABLED_FLAG                ,
            cur.PREGEN_PUTAWAY_TASKS_FLAG       ,
            cur.REGENERATION_INTERVAL           ,
            cur.TIMEZONE_ID                     ,
            cur.MAX_PICKS_BATCH                 ,
            cur.DEFAULT_WMS_PICKING_RULE_ID     ,
            cur.DEFAULT_PUT_AWAY_RULE_ID        ,
            cur.DEFAULT_TASK_ASSIGN_RULE_ID     ,
            cur.DEFAULT_LABEL_COMP_RULE_ID      ,
            cur.DEFAULT_CARTON_RULE_ID          ,
            cur.DEFAULT_CYC_COUNT_HEADER_ID     ,
            cur.CROSSDOCK_FLAG                  ,
            cur.CARTONIZATION_FLAG              ,
            cur.COST_CUTOFF_DATE                ,
            cur.ENABLE_COSTING_BY_CATEGORY      ,
            cur.COST_GROUP_ACCOUNTING           ,
            cur.ALLOCATE_SERIAL_FLAG            ,
            cur.DEFAULT_PICK_TASK_TYPE_ID       ,
            cur.DEFAULT_CC_TASK_TYPE_ID         ,
            cur.DEFAULT_PUTAWAY_TASK_TYPE_ID    ,
            cur.DEFAULT_REPL_TASK_TYPE_ID       ,
            cur.EAM_ENABLED_FLAG                ,
            CAPBRIMP_MAPPING.GetOrganizationMapping(cur.MAINT_ORGANIZATION_ID),
            cur.PRIORITIZE_WIP_JOBS             ,
            cur.DEFAULT_CROSSDOCK_SUBINVENTORY  ,
            cur.SKIP_TASK_WAITING_MINUTES       ,
            cur.QA_SKIPPING_INSP_FLAG           ,
            cur.DEFAULT_CROSSDOCK_LOCATOR_ID    ,
            cur.DEFAULT_MOXFER_TASK_TYPE_ID     ,
            cur.DEFAULT_MOISSUE_TASK_TYPE_ID    ,
            cur.DEFAULT_MATL_OVHD_COST_ID       ,
            cur.DISTRIBUTED_ORGANIZATION_FLAG   ,
            cur.CARRIER_MANIFESTING_FLAG        ,
            cur.DISTRIBUTION_ACCOUNT_ID         ,
            cur.DIRECT_SHIPPING_ALLOWED         ,
            cur.DEFAULT_PICK_OP_PLAN_ID         ,
            cur.MAX_CLUSTERS_ALLOWED            ,
            cur.CONSIGNED_FLAG                  ,
            cur.CARTONIZE_SALES_ORDERS          ,
            cur.CARTONIZE_MANUFACTURING         
       );
     exception
       when others then
     	 l_err_msg_tmp := sqlerrm;
     end;	  	    
     
     if l_err_msg_tmp IS NULL then
       l_processed_count := l_processed_count + 1;  
     else
       l_rejected_count := l_rejected_count + 1;
       l_err_msg := l_err_msg_tmp; 
     end if;
   end loop;

   if l_rejected_count = 0 then
   	 commit;
   else
     rollback;
   end if;
   
   -- insert into log table
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
    values ('CAPPOIMP_ORGANIZATION_ACCESS', sysdate, l_processed_count, l_rejected_count, l_processed_count-l_rejected_count, l_err_msg );
   
   commit;
exception 
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;  
end;
/


--exit
